#include<stdio.h>
#include<stdlib.h>
#include<math.h>
#include<fftw3.h>
#include <iostream>
#include <fstream>
#include <sstream>
#include <string.h>
#include <mysql.h>
#include <sys/time.h>

using namespace std;

class proceso
{
 public:
  proceso(long m=36)
  {
    Meses=m;
    Pg=new double[Meses];
    Wg=new double[Meses+1];
    in = (fftw_complex*) fftw_malloc(sizeof(fftw_complex) * Meses);
    out = (fftw_complex*) fftw_malloc(sizeof(fftw_complex) * Meses);
  }
  
  bool conectar()
  {
    mysql_init(&BDorigen);
    if (!mysql_real_connect(&BDorigen,"168.176.37.29","oscar","codensa","codensa",0,NULL,0))
    {
        cerr << "Sin conexión : " << mysql_error(&BDorigen);
        return false;
    }
    mysql_init(&BDdestino);
    if (!mysql_real_connect(&BDdestino,"168.176.37.29","oscar","codensa","codensa_test",0,NULL,0))
//    if (!mysql_real_connect(&BDdestino,"localhost","root","rootpassword","codensa_test",0,NULL,0))
    {
        cerr << "Sin conexión : " << mysql_error(&BDdestino);
        return false;
    }
    return true;  
  }
  
  string cadena_meses(string strpre,string strpos,long inicio,long fin)
  {
    char n[200]; 
    string cadena;
    for(long i=inicio;i<=fin;i++)
    {
      sprintf(n,"%ld",i);
      cadena+= strpre;
      cadena+=n;
      cadena+= strpos;
    }
    return cadena;
  }
  
  void consulta(MYSQL* BD,string query)
  {
    if(mysql_real_query(BD,query.c_str(),query.length()))
    {
    }else
    {
//      cout << mysql_error(BD) << ":" << query << "\n";
//      abort();
    }
  }

  double media(double *V,long o,long N)
  {
    double m=0;
    for(long i=o;i<N;i++)
    {
      m+=V[i];
    }
    return m/(N-o);
  }

  double varianza(double *V,double m,long o,long N)
  {
    double v=0;
    for(long i=o;i<N;i++)
    {
      v+=(V[i]-m)*(V[i]-m);
    }
    return (v/(N-o))/m;
  }
  
  void crear_tabla()
  {
    string query="";
    query="DROP TABLE IF EXISTS clientes_deltas";
    consulta(&BDdestino,query);
    query="CREATE TABLE clientes_deltas(NRO_CUENTA varchar(14), ESTRATO smallint(6), CLASE varchar(20)";
    query+=cadena_meses(", P_"," decimal(20,10)",3,Meses);  // delta de consumo relativo al promedio
    query+=", P_M decimal(20,10), P_V decimal(20,10)";
    query+=cadena_meses(", PP_"," decimal(20,10)",3,Meses); // delta de consumo relativo al promedio deflactado globalmente
    query+=", PP_M decimal(20,10), PP_V decimal(20,10)";
    query+=cadena_meses(", dP_"," decimal(20,10)",3,Meses);  // delta de consumo relativo al más reciente 
    query+=", dP_M decimal(20,10), dP_V decimal(20,10)";
    query+=cadena_meses(", dPP_"," decimal(20,10)",3,Meses); // delta de consumo relativo al más reciente deflactado globalmente
    query+=", dPP_M decimal(20,10), dPP_V decimal(20,10)";
    query += ");";
    consulta(&BDdestino,query);

    query="DROP TABLE IF EXISTS clientes_espectros";
    consulta(&BDdestino,query);
    query="CREATE TABLE clientes_espectros(NRO_CUENTA varchar(14), ESTRATO smallint(6), CLASE varchar(20)";
    query+=", M decimal(20,10), V decimal(20,10)"; // energia, media y varianza relativa
    query+=cadena_meses(", W_"," decimal(20,10)",0,(Meses/2)); // espectro de consumo
    query += ");";
    consulta(&BDdestino,query);

  }

  void consumo_global()
  {
    string query="SELECT ";
    query+=cadena_meses("SUM(EAFP_","),",2,Meses);
    query += " Count(*) FROM VW_CLIENTES";  
    consulta(&BDorigen,query);
    MYSQL_RES *res = mysql_store_result(&BDorigen);
    MYSQL_ROW row;
    row = mysql_fetch_row(res);
    {
      for(long i=1;i<Meses;i++)
      {
        Pg[i]=atof(row[i-1])/atof(row[Meses-1]);
      }
    }
    MediaGlobal=media(Pg,1,Meses);
    VarianzaGlobal=varianza(Pg,MediaGlobal,1,Meses);
    in[0][0]=Pg[1];
    in[0][1]=0.0;
    for(long i=1;i<Meses;i++)
    {
      in[i][0]=Pg[i];
      in[i][1]=0.0;
    }
    p = fftw_plan_dft_1d(Meses, in, out, FFTW_FORWARD, FFTW_ESTIMATE);
    fftw_execute(p);
    double Ep[Meses],Et=0.0;
    for(long i=0;i<=Meses;i++)
    {
      Ep[i]=sqrt(out[i][0]*out[i][0]+out[i][1]*out[i][1]);
      Et+=Ep[i];
    }
    for(long i=0;i<Meses;i++)
    {
      Wg[i]=Ep[i]/Et;
    }
//    for(long i=0;i<Meses;i++){cout << i << ":" << Wg[i] << endl;}
  }
  
  void espectro(MYSQL_ROW row, double *W)
  {
    in[0][0]=atof(row[9]);
    in[0][1]=0.0;
    for(long i=1;i<Meses;i++)
    {
      in[i][0]=atof(row[i+8]);
      in[i][1]=0.0;
    }
//    for(long i=0;i<Meses;i++){cout << i << ":" << in[i][0] << " " << in[i][1] << endl;}
    p = fftw_plan_dft_1d(Meses, in, out, FFTW_FORWARD, FFTW_ESTIMATE);
    fftw_execute(p);
    double Ep[Meses],Et=0.0;
    for(long i=0;i<=Meses;i++)
    {
      Ep[i]=sqrt(out[i][0]*out[i][0]+out[i][1]*out[i][1]);
      Et+=Ep[i];
    }
    for(long i=0;i<Meses;i++)
    {
      W[i]=Ep[i]/Et;
    }
//    cout << endl;for(long i=0;i<4;i++){cout << i << ":" << W[i] << endl;}
  }
  
  string cadenaValues(double *P,long o,long N)
  {
    string query="";
    char n[200]; 
    for(long i=o;i<N;i++)
    {
      sprintf(n,", %lf", P[i]);
      query+=n;
    }
    return query;
  }
  
  void insertar(MYSQL_ROW row,double M,double V,double *P,double *PP,double *dP,double *dPP,double mP,double mPP,double mdP,double mdPP,double vP,double vPP,double vdP,double vdPP,double *W)
//  void insertar(MYSQL_ROW row,double *W)
  {
    string query="";
    char n[200];
    query="INSERT INTO clientes_deltas(NRO_CUENTA, ESTRATO, CLASE";
    query+=cadena_meses(", P_","",3,Meses);
    query+=", P_M, P_V";
    query+=cadena_meses(", PP_","",3,Meses);
    query+=", PP_M, PP_V";
    query+=cadena_meses(", dP_","",3,Meses);
    query+=", dP_M, dP_V";
    query+=cadena_meses(", dPP_","",3,Meses);
    query+=", dPP_M, dPP_V";
    query+=") VALUES(";
    query+="'";query+=row[0];query+="', ";
    query+=""; query+=row[4];query+=", ";
    query+="'";query+=row[5];query+="'";
    query+=cadenaValues(P,2,Meses);
    sprintf(n,", %lf",mP);query+=n;
    sprintf(n,", %lf",vP);query+=n;
    query+=cadenaValues(PP,2,Meses);
    sprintf(n,", %lf",mPP);query+=n;
    sprintf(n,", %lf",vPP);query+=n;
    query+=cadenaValues(dP,2,Meses);
    sprintf(n,", %lf",mdP);query+=n;
    sprintf(n,", %lf",vdP);query+=n;
    query+=cadenaValues(dPP,2,Meses);
    sprintf(n,", %lf",mdPP);query+=n;
    sprintf(n,", %lf",vdPP);query+=n;
    query+=");";
//    cout << query << "\n";
    consulta(&BDdestino,query);

    query="INSERT INTO clientes_espectros(NRO_CUENTA, ESTRATO, CLASE";
    query+=", M, V";
    query+=cadena_meses(", W_","",0,(Meses/2));
    query+=") VALUES(";
    query+="'";query+=row[0];query+="', ";
    query+=""; query+=row[4];query+=", ";
    query+="'";query+=row[5];query+="'";
    sprintf(n,", %lf",M);query+=n;
    sprintf(n,", %lf",V);query+=n;
    query+=cadenaValues(W,0,(Meses/2)+1);
    query+=");";
//    cout << query << "\n";
    consulta(&BDdestino,query);

  }

  void cliente(MYSQL_ROW row)
  {
    double p[Meses];
    for(long i=1;i<Meses;i++)
    {
      p[i]=atof(row[i+8]);
    }
    double W[Meses+1];
    double P[Meses],PP[Meses],dP[Meses],dPP[Meses];

    double mediap,varianzap;
    double mediaP,varianzaP;
    double mediaPP,varianzaPP;
    double mediadP,varianzadP;
    double mediadPP,varianzadPP;
    
    mediap=media(p,1,Meses);
    varianzap=varianza(p,mediap,1,Meses);
    for(long i=2;i<Meses;i++)
    {
      P[i]=( p[i]-p[i-1] ) / ( mediap );
      PP[i]=(( p[i]-p[i-1] )*MediaGlobal ) / (mediap * (Pg[i]-Pg[i-1]));
      dP[i]=( p[i]-p[i-1] ) / ( p[i-1] );
      dPP[i]=(( p[i]-p[i-1] )*Pg[i-1] ) / (p[i-1] * (Pg[i]-Pg[i-1]));
    }
    mediaP=media(P,2,Meses);
    varianzaP=varianza(P,mediaP,2,Meses);
    mediaPP=media(PP,2,Meses);
    varianzaPP=varianza(PP,mediaPP,2,Meses);
    mediadP=media(dP,2,Meses);
    varianzadP=varianza(dP,mediadP,2,Meses);
    mediadPP=media(dPP,2,Meses);
    varianzadPP=varianza(dPP,mediadPP,2,Meses);
    espectro(row,W);
    insertar(row,mediap,varianzap,P,PP,dP,dPP,mediaP,mediaPP,mediadP,mediadPP,varianzaP,varianzaPP,varianzadP,varianzadPP,W);
  }
  
  void ciclo()
  {
    string query="SELECT * FROM VW_CLIENTES;";// LIMIT 1000;";
    consulta(&BDorigen,query);
    MYSQL_RES *res = mysql_store_result(&BDorigen);
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res)))
    {
      cliente(row);
    }
  }

  void grabar_global()
  {
    ofstream f;
    f.open("global.csv");
    for(long i=1;i<Meses;i++)
    {
      f << i+1 << "\t" << Pg[i] << "\n";
    }
    f << "Media: " << MediaGlobal << "\n";
    f << "Varianza: " << VarianzaGlobal << "\n";
    for(long i=0;i<(Meses/2)+1;i++)
    {
      f << "W_" << i << "\t" << Wg[i] << "\n";
    }
    f.close();
  }
  
 protected:
  MYSQL BDorigen,BDdestino;
  long Meses; 
  double *Pg,*Wg; // consumo global
  double MediaGlobal,VarianzaGlobal;
  fftw_complex *in, *out;
  fftw_plan p;
};


int main()
{
  struct timeval startTime;
  struct timeval endTime;

  proceso P(36);
  if(P.conectar())
  {
    P.crear_tabla();
    
    gettimeofday(&startTime, NULL);

    P.consumo_global();
    P.ciclo();

    gettimeofday(&endTime, NULL);

    P.grabar_global();

    double tS = startTime.tv_sec*1000000 + (startTime.tv_usec);
    double tE = endTime.tv_sec*1000000  + (endTime.tv_usec);
    cout << "Tiempo: " << (tE-tS)/1e6 << " segs" << endl;
  }
  
  return 0;
}

